package dao;
import java.sql.*;

import Shiti.Userti;
public class UserDao {
    // 加载数据库驱动  com.mysql.jdbc.Driver
    private final String driver = "com.mysql.cj.jdbc.Driver";
    // 获取mysql连接地址
    private final String url = "jdbc:mysql://localhost:3306/test?&useSSL=false&serverTimezone=UTC";
    // 数据名称
    private final String username = "root";
    // 数据库密码
    private final String password = "root";
    //登录
    public  boolean denglu (Userti userti){
        Connection conn = null;
        ResultSet rs=null;
        PreparedStatement statement=null;
        try {
            Class.forName(driver);
            //getConnection()方法，连接MySQL数据库！
            conn = DriverManager.getConnection(url, username, password);
//                if (!conn.isClosed())
//                    System.out.println("数据库连接成功！");
            //创建statement类对象，用来执行SQL语句！
            //要执行的SQL语句
            String sql = "select * from user where acc =?";
            //ResultSet类，用来存放获取的结果集！
            statement=conn.prepareStatement(sql);
            statement.setInt(1,userti.getAcc());
            rs = statement.executeQuery();
            String psw1 = null;
            boolean a=true;
            if (rs.next()) {
                //获取‘姓名’这列数据
                psw1 = rs.getString("psw");
               if (userti.getPsw().equals(psw1)){
                   return true;
               }else {
                   return  false;
               }
            }else {
                return false;
            }
            // rs.close();
            //  conn.close();
        } catch (ClassNotFoundException e) {
            //数据库驱动类异常处理
            e.printStackTrace();
            return  false;
        } catch (SQLException e1) {
            //数据库连接失败异常处理
            e1.printStackTrace();
            return  false;
        } catch (Exception e2) {
            e2.printStackTrace();
            return  false;
        }
        finally {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    //管理员登录
    public  boolean guandenglu (Userti userti){
        Connection conn = null;
        ResultSet rs=null;
        PreparedStatement statement=null;
        try {
            Class.forName(driver);
            //getConnection()方法，连接MySQL数据库！
            conn = DriverManager.getConnection(url, username, password);
//                if (!conn.isClosed())
//                    System.out.println("数据库连接成功！");
            //创建statement类对象，用来执行SQL语句！
            //要执行的SQL语句
            String sql = "select * from user where acc =1";
            //ResultSet类，用来存放获取的结果集！
            statement=conn.prepareStatement(sql);
            rs = statement.executeQuery();
            int acc1 = 0;
            String psw1 = null;
            boolean a=true;
            if (rs.next()) {
                //获取‘姓名’这列数据
                acc1 =rs.getInt("acc");
                psw1 = rs.getString("psw");
                if (userti.getPsw().equals(psw1)){
                    return true;
                }else {
                    return  false;
                }

            }else {
                return false;
            }
            // rs.close();
            //  conn.close();
        } catch (ClassNotFoundException e) {
            //数据库驱动类异常处理
            e.printStackTrace();
            return  false;
        } catch (SQLException e1) {
            //数据库连接失败异常处理
            e1.printStackTrace();
            return  false;
        } catch (Exception e2) {
            e2.printStackTrace();
            return  false;
        }
        finally {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    //查看一个人的信息
    public Userti queryone1(int acc,String psw) {
        Userti userti=null;
        // 获取一个数据的连接
        Connection conn = null;
        ResultSet rs=null;
        PreparedStatement statement=null;
        // 获取连接的一个状态
        try {
            Class.forName(driver);
            //getConnection()方法，连接MySQL数据库！
            conn = DriverManager.getConnection(url, username, password);
//                if (!conn.isClosed())
//                    System.out.println("数据库连接成功！");
            //创建statement类对象，用来执行SQL语句！
            //要执行的SQL语句
            String sql = "select * from user where acc =? and psw = ?";
            //ResultSet类，用来存放获取的结果集！
            statement=conn.prepareStatement(sql);
            statement.setInt(1,acc);
            statement.setString(2,psw);
            rs = statement.executeQuery();
            int acc1 = 0;
            String name1 = null;
            String psw1 = null;
            boolean a=true;
            if (rs.next()) {
                //获取‘学号’这列数据
                acc1 = Integer.parseInt(rs.getString("acc"));
                //获取‘姓名’这列数据
                psw1 = rs.getString("psw");
                //获取‘数学成绩’这列数据
                name1 = rs.getString("name");
                userti =new Userti(acc1,psw1,name1);
                return userti;

            }else {
                return  null;
            }
            // rs.close();
            //  conn.close();
        } catch (ClassNotFoundException e) {
            //数据库驱动类异常处理
            e.printStackTrace();
            return  null;
        } catch (SQLException e1) {
            //数据库连接失败异常处理
            e1.printStackTrace();
            return  null;
        } catch (Exception e2) {
            e2.printStackTrace();
            return  null;
        }
        finally {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
    //管理员查看信息个人
    public  Userti  query(int acc) {
        Userti userti=null;
        // 获取一个数据的连接
        Connection conn = null;
        ResultSet rs=null;
        PreparedStatement statement=null;
        // 获取连接的一个状态
        try {
            Class.forName(driver);
            //getConnection()方法，连接MySQL数据库！
            conn = DriverManager.getConnection(url, username, password);
//                if (!conn.isClosed())
//                    System.out.println("数据库连接成功！");
            //创建statement类对象，用来执行SQL语句！
            //要执行的SQL语句
            String sql = "select * from user where acc =?";
            //ResultSet类，用来存放获取的结果集！
            statement=conn.prepareStatement(sql);
            statement.setInt(1,acc);
            rs = statement.executeQuery();
            int acc1 = 0;
            String name1 = null;
            String psw1 = null;
            if (rs.next()) {
                //获取‘账号’这列数据
                acc1 = Integer.parseInt(rs.getString("acc"));
                //获取‘密码’这列数据
                psw1 = rs.getString("psw");
                //获取‘name’这列数据
                name1 = rs.getString("name");
                userti =new Userti(acc1,psw1,name1);

            }
            return userti;
            // rs.close();
            //  conn.close();
        } catch (ClassNotFoundException e) {
            //数据库驱动类异常处理
            e.printStackTrace();
            return  null;
        } catch (SQLException e1) {
            //数据库连接失败异常处理
            e1.printStackTrace();
            return  null;
        } catch (Exception e2) {
            e2.printStackTrace();
            return  null;
        }
        finally {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
    //添加
    public  boolean add(Userti userti){
        Connection conn = null;
        ResultSet rs=null;
        PreparedStatement statement=null;
        // 获取连接的一个状态
        try{
            Class.forName(driver);
            //getConnection()方法，连接MySQL数据库！
            conn=DriverManager.getConnection(url,username,password);
            //创建statement类对象，用来执行SQL语句！
            //要执行的SQL语句
            String sql ="insert into user values (?,?,? )";
            statement=conn.prepareStatement(sql);
            statement.setInt(1,userti.getAcc());
            statement.setString(2,userti.getPsw());
            statement.setString(3,userti.getName());
            statement.execute();
            return true;
        }
        catch(ClassNotFoundException e){
            //数据库驱动类异常处理
            e.printStackTrace();
            return false;
        }
        catch(SQLException e1){
            //数据库连接失败异常处理
            e1.printStackTrace();
            return false;
        }
        catch(Exception e2){
            e2.printStackTrace();
            return false;
        }
        finally {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
    //删除
    public  boolean dele(Userti userti){
        Connection conn = null;
        ResultSet rs=null;
        PreparedStatement statement=null;
        // 获取连接的一个状态
        try{
            Class.forName(driver);
            //getConnection()方法，连接MySQL数据库！
            conn=DriverManager.getConnection(url,username,password);
            //创建statement类对象，用来执行SQL语句！
            //要执行的SQL语句
            String sql ="delete from user where acc = ?";
            statement=conn.prepareStatement(sql);
            statement.setInt(1,userti.getAcc());
            //ResultSet类，用来存放获取的结果集！
            statement.execute();
            return true;
        }
        catch(ClassNotFoundException e){
            //数据库驱动类异常处理
            e.printStackTrace();
            return false;
        }
        catch(SQLException e1){
            //数据库连接失败异常处理
            e1.printStackTrace();
            return false;
        }
        catch(Exception e2){
            e2.printStackTrace();
            return false;
        }
        finally {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
    //改密码
    public  boolean changepsw(Userti userti,String psw){
        Connection conn = null;
        ResultSet rs=null;
        PreparedStatement statement=null;
        // 获取连接的一个状态
        try{
            Class.forName(driver);
            //getConnection()方法，连接MySQL数据库！
            conn=DriverManager.getConnection(url,username,password);
            //创建statement类对象，用来执行SQL语句！
            //要执行的SQL语句
            String sql ="update user set psw =? where acc = ?";
            statement=conn.prepareStatement(sql);
            statement.setString(1,psw);
            statement.setInt(2,userti.getAcc());
            //ResultSet类，用来存放获取的结果集！
            statement.execute();
            return true;
        }
        catch(ClassNotFoundException e){
            //数据库驱动类异常处理
            e.printStackTrace();
            return false;
        }
        catch(SQLException e1){
            //数据库连接失败异常处理
            e1.printStackTrace();
            return false;
        }
        catch(Exception e2){
            e2.printStackTrace();
            return false;
        }
        finally {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    //改name
    public  boolean changename(Userti userti,String name){
        Connection conn = null;
        ResultSet rs=null;
        PreparedStatement statement=null;
        // 获取连接的一个状态
        try{
            Class.forName(driver);
            //getConnection()方法，连接MySQL数据库！
            conn=DriverManager.getConnection(url,username,password);
            //创建statement类对象，用来执行SQL语句！
            //要执行的SQL语句
            String sql ="update user set name =? where acc = ?";
            statement=conn.prepareStatement(sql);
            statement.setString(1,name);
            statement.setInt(2,userti.getAcc());
            //ResultSet类，用来存放获取的结果集！
            statement.execute();
            return true;
        }
        catch(ClassNotFoundException e){
            //数据库驱动类异常处理
            e.printStackTrace();
            return false;
        }
        catch(SQLException e1){
            //数据库连接失败异常处理
            e1.printStackTrace();
            return false;
        }
        catch(Exception e2){
            e2.printStackTrace();
            return false;
        }
        finally {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
}
